/* GsStocksDao.java
 *
 * Project/Product: JZCG-XMBUS
 *
 * Modify Information:
 * =============================================================================
 *   Author	Date       Description
 *   ----------	---------- ---------------------------------------------------
 *   peng.ning	2008-8-26	First release
 *
 * Licensed Materials - Property of APEP
 * =============================================================================
 *    (C) COPYRIGHT Shanghai Auto Mart E-Procurement Co., Ltd. 2007 - 2008
 *    All Rights Reserved.
 *
 *    This software is the confidential and proprietary information of
 *    Shanghai Auto Mart E-Procurement Co., Ltd. ("Confidential Information").
 *    You shall not disclose such Confidential Information and shall use it only
 *    in accordance with the terms of the license agreement you entered into 
 *    with Shanghai Auto Mart E-Procurement Co., Ltd.
 *
 * Noties:
 * =============================================================================
 * 
 */

package com.hitrust.gs.dao;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;

import com.hitrust.gs.bean.GSBalanceMsgBean;
import com.hitrust.gs.bean.GSManufactureBean;
import com.hitrust.gs.bean.GSOperatorBean;
import com.hitrust.gs.bean.GSOrderBean;
import com.hitrust.gs.bean.GSOrderDetailBean;
import com.hitrust.gs.bean.GSOrganizationBean;
import com.hitrust.gs.bean.GSPayReceiveAbleBean;
import com.hitrust.gs.bean.GSProviderBean;
import com.hitrust.gs.bean.GSStocksBean;
import com.hitrust.gs.bean.GSStocksDetailBean;
import com.hitrust.gs.bean.GSStocksOtherBean;
import com.hitrust.gs.form.GSStocksDetailForm;
import com.hitrust.gs.lib.GSBaseConstants;
import com.hitrust.gs.lib.GSLogWriter;
import com.hitrust.gs.lib.GSSystemConfigurator;
import com.hitrust.gs.lib.db.GSBaseDAO;
import com.hitrust.gs.util.date.DateUtil;
import com.hitrust.gs.util.exception.GSException;
import com.hitrust.gs.vo.GSBalanceInfoVO;
import com.hitrust.gs.vo.GSStocksDetailVO;
import com.hitrust.gs.vo.GSStocksVO;

public class GsStocksDao extends GSBaseDAO {
    /***************************************************************************
     * 取得压库结算基本信息表sql
     * 
     * @param GSStocksVO
     * 
     * @return
     */
    private String getStocksResultSql(GSStocksVO svo) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.gs_stocks_id as p1,c.gs_detail_id as p1_,b.gs_org_name as p2,a.gs_workcorp_id as p2_,");
        sql.append("a.gs_stocks_person as p3,gs_stocks_dept as p4,c.gs_concentricity_id as p5,c.gs_manufacture_id as p6,");
        sql.append("c.gs_manufacture_name as p7,c.gs_manufacture_type as p8,c.gs_manufacture_category_id as p9,");
        sql.append("d.gs_manufacture_category_name as p10,c.gs_provider_id as p11,e.gs_provider_shortname as p12,");
        sql.append("c.gs_detail_unitprice as p13,c.gs_detail_count as p14,a.gs_stocks_date as p15,a.gs_operator_date as p16,c.gs_detail_state as p17,");
        sql.append("a.gs_shipment_id as p18,(select gs_org_name from GS_ORGANIZATION where gs_org_id=a.gs_shipment_id) as p18_,c.gs_balance_mode as P19 ");
        sql.append("from  GS_STOCKS_BALANCE a,GS_ORGANIZATION b, GS_STOCKS_BALANCE_DETAIL c,");
        sql.append("gs_manufacture_category d,gs_provider e,gs_manufacture_filter f where a.gs_workcorp_id=b.gs_org_id ");
        sql.append("and a.gs_stocks_id=c.gs_stocks_id and c.gs_manufacture_category_id=d.gs_manufacture_category_id ");
        sql.append("and c.gs_provider_id=e.gs_provider_id and c.gs_manufacture_id=f.gs_manufacture_id and a.gs_stocks_state='0'");
        String st = "0";
        if (svo != null) {
            if (svo.gs_stocks_state != null && !svo.gs_stocks_state.trim().equals("")) {
                st = svo.gs_stocks_state.trim();
            }
            if (svo.gs_stocks_state == null) {
                st = "";
            }
            if (svo.gs_month != null && !svo.gs_month.trim().equals("")) {
                st = "1";
            }
            sql.append(" and c.gs_detail_state like '%" + st + "%' ");
            // 判断VO对象中产品种类是否存在
            if (svo.gs_manu_catid1 != null) {
                if (svo.gs_manu_catid1.equals("0")) {
                    sql.append(" and c.gs_manufacture_category_id like '%%' ");
                } else if (svo.gs_manu_catid2.equals("0")) {
                    sql.append(" and (d.gs_manufacture_category_id like '%" + svo.gs_manu_catid1 + "%' or d.gs_manufacture_category_id1 like '%"
                            + svo.gs_manu_catid1 + "%') ");
                } else if (svo.gs_manu_catid3.equals("0")) {
                    sql.append(" and (d.gs_manufacture_category_id like '%" + svo.gs_manu_catid2 + "%' or d.gs_manufacture_category_id2 like '%"
                            + svo.gs_manu_catid2 + "%') ");
                } else {
                    sql.append(" and c.gs_manufacture_category_id like '%" + svo.gs_manu_catid3 + "%' ");
                }
            }
            if (svo.gs_workcorp_id != null && !svo.gs_workcorp_id.trim().equals("")) {
                sql.append(" and a.GS_WORKCORP_ID like '%" + svo.gs_workcorp_id.trim() + "%' ");
            }
            if (svo.gs_proid != null && !svo.gs_proid.trim().equals("")) {
                sql.append(" and c.gs_provider_id like '%" + svo.gs_proid + "%' ");
            }
            if (svo.gs_stocks_id != null && !svo.gs_stocks_id.trim().equals("")) {
                sql.append(" and (a.gs_stocks_id like '%" + svo.gs_stocks_id.trim() + "%' or c.gs_detail_id like '%" + svo.gs_stocks_id.trim() + "%') ");
            }
            if (svo.gs_stocks_person != null && !svo.gs_stocks_person.trim().equals("")) {
                sql.append(" and a.gs_stocks_person like '%" + svo.gs_stocks_person.trim() + "%' ");
            }
            if (svo.gs_manuname != null && !svo.gs_manuname.trim().equals("")) {
                sql.append(" and f.gs_manufacture_name like '%" + svo.gs_manuname.trim() + "%' ");
            }
            if (svo.gs_manuconid != null && svo.gs_manuconid.trim().equals("")) {
                sql.append(" and c.gs_concentricity_id like '%" + svo.gs_manuconid.trim() + "%'");
            }
            if (svo.gs_stocks_datestart != null && !svo.gs_stocks_datestart.trim().equals("")) {
                sql.append(" and to_char(to_date(a.GS_stocks_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') >='" + svo.gs_stocks_datestart.trim() + "' ");
            }
            if (svo.gs_stocks_dateend != null && !svo.gs_stocks_dateend.trim().equals("")) {
                sql.append(" and to_char(to_date(a.GS_stocks_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') <='" + svo.gs_stocks_dateend.trim() + "' ");
            }
            if (svo.gs_stocks_dept != null && !svo.gs_stocks_dept.trim().equals("")) {
                sql.append("and a.gs_stocks_dept like '%" + svo.gs_stocks_dept.trim() + "%' ");
            }
            if (svo.gs_month != null && !svo.gs_month.trim().equals("")) {
                sql.append(" and c.gs_balance_month='" + svo.gs_month.trim() + "' ");
            }
            if (svo.gs_shipment_id != null && !svo.gs_shipment_id.trim().equals("")) {
                sql.append(" and a.gs_shipment_id like '%" + svo.gs_shipment_id + "%' ");
            } else if (svo.arr != null && svo.arr.size() > 0) {
                sql.append(" and a.gs_shipment_id in ( ");
                String s = "";
                for (int i = 0; i < svo.arr.size(); i++) {
                    GSOrganizationBean orgbean = (GSOrganizationBean) svo.arr.get(i);
                    s += "'" + orgbean.getGs_org_id() + "',";
                }
                sql.append(s.subSequence(0, s.length() - 1) + " )");
            }
        }
        sql.append(" order by a.gs_operator_date desc");
        return sql.toString();
    }

    /***************************************************************************
     * 取得压库结算基本信息表sql(运营公司)
     * 
     * @param GSStocksVO
     * 
     * @return
     */
    private String getStocksResultSql2(GSStocksVO svo) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.gs_stocks_id as p1,c.gs_detail_id as p1_,b.gs_org_name as p2,a.gs_workcorp_id as p2_,");
        sql.append("a.gs_stocks_person as p3,gs_stocks_dept as p4,c.gs_concentricity_id as p5,c.gs_manufacture_id as p6,");
        sql.append("c.gs_manufacture_name as p7,c.gs_manufacture_type as p8,c.gs_manufacture_category_id as p9,");
        sql.append("d.gs_manufacture_category_name as p10,c.gs_provider_id as p11,e.gs_provider_shortname as p12,");
        sql.append("c.gs_detail_unitprice as p13,c.gs_detail_count as p14,a.gs_stocks_date as p15,a.gs_operator_date as p16,c.gs_detail_state as p17,");
        sql.append("a.gs_shipment_id as p18,(select gs_org_name from GS_ORGANIZATION where gs_org_id=a.gs_shipment_id) as p18_,c.gs_balance_mode as P19 ");
        sql.append("from  GS_STOCKS_BALANCE a,GS_ORGANIZATION b, GS_STOCKS_BALANCE_DETAIL c,");
        sql.append("gs_manufacture_category d,gs_provider e,gs_manufacture_filter f where a.gs_workcorp_id=b.gs_org_id ");
        sql.append("and a.gs_stocks_id=c.gs_stocks_id and c.gs_manufacture_category_id=d.gs_manufacture_category_id ");
        sql.append("and c.gs_provider_id=e.gs_provider_id and c.gs_manufacture_id=f.gs_manufacture_id and a.gs_stocks_state='0'");
        String st = "0";
        if (svo != null) {
            if (svo.gs_stocks_state != null && !svo.gs_stocks_state.trim().equals("")) {
                st = svo.gs_stocks_state.trim();
            }
            if (svo.gs_stocks_state == null) {
                st = "";
            }
            if (svo.gs_month != null && !svo.gs_month.trim().equals("")) {
                st = "1";
            }
            sql.append(" and c.gs_detail_state like '%" + st + "%' ");
            // 判断VO对象中产品种类是否存在
            if (svo.gs_manu_catid1 != null) {
                if (svo.gs_manu_catid1.equals("0")) {
                    sql.append(" and c.gs_manufacture_category_id like '%%' ");
                } else if (svo.gs_manu_catid2.equals("0")) {
                    sql.append(" and (d.gs_manufacture_category_id like '%" + svo.gs_manu_catid1 + "%' or d.gs_manufacture_category_id1 like '%"
                            + svo.gs_manu_catid1 + "%') ");
                } else if (svo.gs_manu_catid3.equals("0")) {
                    sql.append(" and (d.gs_manufacture_category_id like '%" + svo.gs_manu_catid2 + "%' or d.gs_manufacture_category_id2 like '%"
                            + svo.gs_manu_catid2 + "%') ");
                } else {
                    sql.append(" and c.gs_manufacture_category_id like '%" + svo.gs_manu_catid3 + "%' ");
                }
            }
            if (svo.gs_workcorp_id != null && !svo.gs_workcorp_id.trim().equals("")) {
                sql.append(" and a.gs_workcorp_id like '%" + svo.gs_workcorp_id + "%' ");
            } else if (svo.arr != null && svo.arr.size() > 0) {
                sql.append(" and a.gs_workcorp_id in ( ");
                String s = "";
                for (int i = 0; i < svo.arr.size(); i++) {
                    GSOrganizationBean orgbean = (GSOrganizationBean) svo.arr.get(i);
                    s += "'" + orgbean.getGs_org_id() + "',";
                }
                sql.append(s.subSequence(0, s.length() - 1) + " )");
            }
            if (svo.gs_proid != null && !svo.gs_proid.trim().equals("")) {
                sql.append(" and c.gs_provider_id like '%" + svo.gs_proid + "%' ");
            }
            if (svo.gs_stocks_id != null && !svo.gs_stocks_id.trim().equals("")) {
                sql.append(" and (a.gs_stocks_id like '%" + svo.gs_stocks_id.trim() + "%' or c.gs_detail_id like '%" + svo.gs_stocks_id.trim() + "%') ");
            }
            if (svo.gs_stocks_person != null && !svo.gs_stocks_person.trim().equals("")) {
                sql.append(" and a.gs_stocks_person like '%" + svo.gs_stocks_person.trim() + "%' ");
            }
            if (svo.gs_manuname != null && !svo.gs_manuname.trim().equals("")) {
                sql.append(" and f.gs_manufacture_name like '%" + svo.gs_manuname.trim() + "%' ");
            }
            if (svo.gs_manuconid != null && svo.gs_manuconid.trim().equals("")) {
                sql.append(" and c.gs_concentricity_id like '%" + svo.gs_manuconid.trim() + "%'");
            }
            if (svo.gs_stocks_datestart != null && !svo.gs_stocks_datestart.trim().equals("")) {
                sql.append(" and to_char(to_date(a.GS_stocks_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') >='" + svo.gs_stocks_datestart.trim() + "' ");
            }
            if (svo.gs_stocks_dateend != null && !svo.gs_stocks_dateend.trim().equals("")) {
                sql.append(" and to_char(to_date(a.GS_stocks_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') <='" + svo.gs_stocks_dateend.trim() + "' ");
            }
            if (svo.gs_stocks_dept != null && !svo.gs_stocks_dept.trim().equals("")) {
                sql.append("and a.gs_stocks_dept like '%" + svo.gs_stocks_dept.trim() + "%' ");
            }
            if (svo.gs_month != null && !svo.gs_month.trim().equals("")) {
                sql.append(" and c.gs_balance_month='" + svo.gs_month.trim() + "' ");
            }
            if (svo.gs_shipment_id != null && !svo.gs_shipment_id.trim().equals("")) {
                sql.append(" and a.gs_shipment_id like '%" + svo.gs_shipment_id.trim() + "%' ");
            }
        }
        sql.append(" order by a.gs_operator_date desc");
        return sql.toString();
    }
    /***************************************************************************
     * 取得压库结算基本信息表sql(不判断状态)
     * 
     * @param GSStocksVO
     * 
     * @return
     */
    private String getStocksResultSql3(GSStocksVO svo) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.gs_stocks_id as p1,c.gs_detail_id as p1_,b.gs_org_name as p2,a.gs_workcorp_id as p2_,");
        sql.append("a.gs_stocks_person as p3,gs_stocks_dept as p4,c.gs_concentricity_id as p5,c.gs_manufacture_id as p6,");
        sql.append("c.gs_manufacture_name as p7,c.gs_manufacture_type as p8,c.gs_manufacture_category_id as p9,");
        sql.append("d.gs_manufacture_category_name as p10,c.gs_provider_id as p11,e.gs_provider_shortname as p12,");
        sql.append("c.gs_detail_unitprice as p13,c.gs_detail_count as p14,a.gs_stocks_date as p15,a.gs_operator_date as p16,c.gs_detail_state as p17,");
        sql.append("a.gs_shipment_id as p18,(select gs_org_name from GS_ORGANIZATION where gs_org_id=a.gs_shipment_id) as p18_,c.gs_balance_mode as P19 ");
        sql.append("from  GS_STOCKS_BALANCE a,GS_ORGANIZATION b, GS_STOCKS_BALANCE_DETAIL c,");
        sql.append("gs_manufacture_category d,gs_provider e,gs_manufacture_filter f where a.gs_workcorp_id=b.gs_org_id ");
        sql.append("and a.gs_stocks_id=c.gs_stocks_id and c.gs_manufacture_category_id=d.gs_manufacture_category_id ");
        sql.append("and c.gs_provider_id=e.gs_provider_id and c.gs_manufacture_id=f.gs_manufacture_id and a.gs_stocks_state='0'");
        if (svo != null) {
            sql.append(" and c.gs_detail_state>'0' and c.gs_detail_state<>'2' ");
            // 判断VO对象中产品种类是否存在
            if (svo.gs_manu_catid1 != null) {
                if (svo.gs_manu_catid1.equals("0")) {
                    sql.append(" and c.gs_manufacture_category_id like '%%' ");
                } else if (svo.gs_manu_catid2.equals("0")) {
                    sql.append(" and (d.gs_manufacture_category_id like '%" + svo.gs_manu_catid1 + "%' or d.gs_manufacture_category_id1 like '%"
                            + svo.gs_manu_catid1 + "%') ");
                } else if (svo.gs_manu_catid3.equals("0")) {
                    sql.append(" and (d.gs_manufacture_category_id like '%" + svo.gs_manu_catid2 + "%' or d.gs_manufacture_category_id2 like '%"
                            + svo.gs_manu_catid2 + "%') ");
                } else {
                    sql.append(" and c.gs_manufacture_category_id like '%" + svo.gs_manu_catid3 + "%' ");
                }
            }
            if (svo.gs_workcorp_id != null && !svo.gs_workcorp_id.trim().equals("")) {
                sql.append(" and a.GS_WORKCORP_ID like '%" + svo.gs_workcorp_id.trim() + "%' ");
            }
            if (svo.gs_proid != null && !svo.gs_proid.trim().equals("")) {
                sql.append(" and c.gs_provider_id like '%" + svo.gs_proid + "%' ");
            }
            if (svo.gs_stocks_id != null && !svo.gs_stocks_id.trim().equals("")) {
                sql.append(" and (a.gs_stocks_id like '%" + svo.gs_stocks_id.trim() + "%' or c.gs_detail_id like '%" + svo.gs_stocks_id.trim() + "%') ");
            }
            if (svo.gs_stocks_person != null && !svo.gs_stocks_person.trim().equals("")) {
                sql.append(" and a.gs_stocks_person like '%" + svo.gs_stocks_person.trim() + "%' ");
            }
            if (svo.gs_manuname != null && !svo.gs_manuname.trim().equals("")) {
                sql.append(" and f.gs_manufacture_name like '%" + svo.gs_manuname.trim() + "%' ");
            }
            if (svo.gs_manuconid != null && svo.gs_manuconid.trim().equals("")) {
                sql.append(" and c.gs_concentricity_id like '%" + svo.gs_manuconid.trim() + "%'");
            }
            if (svo.gs_stocks_datestart != null && !svo.gs_stocks_datestart.trim().equals("")) {
                sql.append(" and to_char(to_date(a.GS_stocks_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') >='" + svo.gs_stocks_datestart.trim() + "' ");
            }
            if (svo.gs_stocks_dateend != null && !svo.gs_stocks_dateend.trim().equals("")) {
                sql.append(" and to_char(to_date(a.GS_stocks_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD') <='" + svo.gs_stocks_dateend.trim() + "' ");
            }
            if (svo.gs_stocks_dept != null && !svo.gs_stocks_dept.trim().equals("")) {
                sql.append("and a.gs_stocks_dept like '%" + svo.gs_stocks_dept.trim() + "%' ");
            }
            if (svo.gs_month != null && !svo.gs_month.trim().equals("")) {
                sql.append(" and c.gs_balance_month='" + svo.gs_month.trim() + "' ");
            }
            if (svo.gs_shipment_id != null && !svo.gs_shipment_id.trim().equals("")) {
                sql.append(" and a.gs_shipment_id like '%" + svo.gs_shipment_id + "%' ");
            } else if (svo.arr != null && svo.arr.size() > 0) {
                sql.append(" and a.gs_shipment_id in ( ");
                String s = "";
                for (int i = 0; i < svo.arr.size(); i++) {
                    GSOrganizationBean orgbean = (GSOrganizationBean) svo.arr.get(i);
                    s += "'" + orgbean.getGs_org_id() + "',";
                }
                sql.append(s.subSequence(0, s.length() - 1) + " )");
            }
        }
        sql.append(" order by a.gs_operator_date desc");
        return sql.toString();
    }
    private String getStocksDetailBalanceResultSql(String[] str) {
        StringBuffer sql = new StringBuffer();
        int a = str.length;
        String sta = "1";
        if (a > 2) {
            sta = str[2];
        }
        sql.append("select a.gs_stocks_id as p1,c.gs_detail_id as p1_,b.gs_org_name as p2,a.gs_workcorp_id as p2_,");
        sql.append("a.gs_stocks_person as p3,gs_stocks_dept as p4,c.gs_concentricity_id as p5,c.gs_manufacture_id as p6,");
        sql.append("c.gs_manufacture_name as p7,c.gs_manufacture_type as p8,c.gs_manufacture_category_id as p9,");
        sql.append("d.gs_manufacture_category_name as p10,c.gs_provider_id as p11,e.gs_provider_shortname as p12,");
        sql.append("c.gs_detail_unitprice as p13,c.gs_detail_count as p14,a.gs_stocks_date as p15,a.gs_operator_date as p16,c.gs_detail_state as p17,");
        sql.append("a.gs_shipment_id as p18,(select gs_org_name from GS_ORGANIZATION where gs_org_id=a.gs_shipment_id) as p18_,c.gs_balance_mode as P19 ");
        sql.append("from  GS_STOCKS_BALANCE a,GS_ORGANIZATION b, GS_STOCKS_BALANCE_DETAIL c,");
        sql.append("gs_manufacture_category d,gs_provider e,gs_manufacture_filter f where a.gs_workcorp_id=b.gs_org_id ");
        sql.append("and a.gs_stocks_id=c.gs_stocks_id and c.gs_manufacture_category_id=d.gs_manufacture_category_id ");
        sql.append("and c.gs_provider_id=e.gs_provider_id and c.gs_manufacture_id=f.gs_manufacture_id ");
        sql.append(" and a.gs_workcorp_id='" + str[0] + "' and c.gs_balance_month='" + str[1] + "' and c.gs_detail_state='" + sta + "'");
        sql.append(" order by a.gs_operator_date desc");
        return sql.toString();
    }
    private String getStocksDetailBalanceResultSql2(String[] str) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.gs_stocks_id as p1,c.gs_detail_id as p1_,b.gs_org_name as p2,a.gs_workcorp_id as p2_,");
        sql.append("a.gs_stocks_person as p3,gs_stocks_dept as p4,c.gs_concentricity_id as p5,c.gs_manufacture_id as p6,");
        sql.append("c.gs_manufacture_name as p7,c.gs_manufacture_type as p8,c.gs_manufacture_category_id as p9,");
        sql.append("d.gs_manufacture_category_name as p10,c.gs_provider_id as p11,e.gs_provider_shortname as p12,");
        sql.append("c.gs_detail_unitprice as p13,c.gs_detail_count as p14,a.gs_stocks_date as p15,a.gs_operator_date as p16,c.gs_detail_state as p17,");
        sql.append("a.gs_shipment_id as p18,(select gs_org_name from GS_ORGANIZATION where gs_org_id=a.gs_shipment_id) as p18_,c.gs_balance_mode as P19 ");
        sql.append("from  GS_STOCKS_BALANCE a,GS_ORGANIZATION b, GS_STOCKS_BALANCE_DETAIL c,");
        sql.append("gs_manufacture_category d,gs_provider e,gs_manufacture_filter f where a.gs_workcorp_id=b.gs_org_id ");
        sql.append("and a.gs_stocks_id=c.gs_stocks_id and c.gs_manufacture_category_id=d.gs_manufacture_category_id ");
        sql.append("and c.gs_provider_id=e.gs_provider_id and c.gs_manufacture_id=f.gs_manufacture_id ");
        sql.append(" and c.gs_balance_month='" + str[1] + "'");
        if (str[2].equals("org")) {
            sql.append(" and c.gs_org_state='1'" );
        }else{
            sql.append(" and c.gs_prov_state='1'" );
        }
        //过滤结算模式为压库的信息
        if(str.length>3){
            sql.append(" and c.gs_balance_mode='"+str[3]+"' and c.gs_provider_id='"+str[0]+"'" );//供应商
        }else{
            sql.append(" and a.gs_workcorp_id='" + str[0] + "'" );//领用单位
        }
        sql.append(" order by a.gs_operator_date desc");
        return sql.toString();
    }
    private String getStocksDetailBalanceResultSql3(String[] str) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.gs_stocks_id as p1,c.gs_detail_id as p1_,b.gs_org_name as p2,a.gs_workcorp_id as p2_,");
        sql.append("a.gs_stocks_person as p3,gs_stocks_dept as p4,c.gs_concentricity_id as p5,c.gs_manufacture_id as p6,");
        sql.append("c.gs_manufacture_name as p7,c.gs_manufacture_type as p8,c.gs_manufacture_category_id as p9,");
        sql.append("d.gs_manufacture_category_name as p10,c.gs_provider_id as p11,e.gs_provider_shortname as p12,");
        sql.append("c.gs_detail_unitprice as p13,c.gs_detail_count as p14,a.gs_stocks_date as p15,a.gs_operator_date as p16,c.gs_detail_state as p17,");
        sql.append("a.gs_shipment_id as p18,(select gs_org_name from GS_ORGANIZATION where gs_org_id=a.gs_shipment_id) as p18_,c.gs_balance_mode as P19 ");
        sql.append("from  GS_STOCKS_BALANCE a,GS_ORGANIZATION b, GS_STOCKS_BALANCE_DETAIL c,");
        sql.append("gs_manufacture_category d,gs_provider e,gs_manufacture_filter f where a.gs_workcorp_id=b.gs_org_id ");
        sql.append("and a.gs_stocks_id=c.gs_stocks_id and c.gs_manufacture_category_id=d.gs_manufacture_category_id ");
        sql.append("and c.gs_provider_id=e.gs_provider_id and c.gs_manufacture_id=f.gs_manufacture_id ");
        sql.append(" and to_char(to_date(a.GS_stocks_DATE,'YYYY-MM-DD HH24:mi:ss'),'YYYY-MM-DD')<='"+str[0]+"'");
        if (str[1].equals("org")) {
            sql.append(" and c.gs_org_state='0'  and a.gs_workcorp_id='" + str[2] + "'" );
        }else{
            sql.append(" and c.gs_prov_state='0' and c.gs_provider_id='"+str[2]+"'" );
        }
        //过滤结算模式为压库的信息
        if(str.length>3){
            sql.append(" and c.gs_balance_mode='"+str[3]+"'" );//供应商
        }
        sql.append(" order by a.gs_operator_date desc");
        return sql.toString();
    }
    private String getStocksDetailBalanceResultSql4(String[] str) {
        StringBuffer sql = new StringBuffer();
        sql.append("select a.gs_stocks_id as p1,c.gs_detail_id as p1_,b.gs_org_name as p2,a.gs_workcorp_id as p2_,");
        sql.append("a.gs_stocks_person as p3,gs_stocks_dept as p4,c.gs_concentricity_id as p5,c.gs_manufacture_id as p6,");
        sql.append("c.gs_manufacture_name as p7,c.gs_manufacture_type as p8,c.gs_manufacture_category_id as p9,");
        sql.append("d.gs_manufacture_category_name as p10,c.gs_provider_id as p11,e.gs_provider_shortname as p12,");
        sql.append("c.gs_detail_unitprice as p13,c.gs_detail_count as p14,a.gs_stocks_date as p15,a.gs_operator_date as p16,c.gs_detail_state as p17,");
        sql.append("a.gs_shipment_id as p18,(select gs_org_name from GS_ORGANIZATION where gs_org_id=a.gs_shipment_id) as p18_,c.gs_balance_mode as P19 ");
        sql.append("from  GS_STOCKS_BALANCE a,GS_ORGANIZATION b, GS_STOCKS_BALANCE_DETAIL c,");
        sql.append("gs_manufacture_category d,gs_provider e,gs_manufacture_filter f where a.gs_workcorp_id=b.gs_org_id ");
        sql.append("and a.gs_stocks_id=c.gs_stocks_id and c.gs_manufacture_category_id=d.gs_manufacture_category_id ");
        sql.append("and c.gs_provider_id=e.gs_provider_id and c.gs_manufacture_id=f.gs_manufacture_id and gs_detail_state>='1' and gs_detail_state<>'2' ");
        sql.append(" and c.gs_provider_id='" + str[0] + "' and c.gs_balance_month='" + str[1] + "' and c.gs_balance_mode='" + str[2] + "'");
        sql.append(" order by a.gs_operator_date desc");
        return sql.toString();
    }
    public ArrayList getStocksDetailBalanceResult3(String[] str) throws GSException {
        String sql = this.getStocksDetailBalanceResultSql3(str);
        try {
            ArrayList array = this.executeQuery(new GSStocksOtherBean(), sql, null);
            return array;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }
    public ArrayList getStocksDetailBalanceResult2(String[] str) throws GSException {
        String sql = this.getStocksDetailBalanceResultSql2(str);
        try {
            ArrayList array = this.executeQuery(new GSStocksOtherBean(), sql, null);
            return array;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }
    public ArrayList getStocksDetailBalanceResult(String[] str) throws GSException {
        String sql = this.getStocksDetailBalanceResultSql(str);
        try {
            ArrayList array = this.executeQuery(new GSStocksOtherBean(), sql, null);
            return array;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }
    
    public ArrayList getStocksDetailBalanceResult(String[] str, int pageNo, int pageSize) throws GSException {
        String sql = this.getStocksDetailBalanceResultSql(str);
        try {
            ArrayList array = this.executeQuery(new GSStocksOtherBean(), sql, null, pageNo, pageSize);
            return array;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }

    public int getStocksDetailBalanceResultRow(String[] str) throws GSException {
        String sql = this.getStocksDetailBalanceResultSql(str);
        try {
            int row = this.queryRowNum("select count(*) from (" + sql + ")", null);
            return row;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }
    /**
     * 根据纳入结算月份及供应商编号查询所有已纳入结算，结算模式为压库的领用明细
     * */
    public ArrayList getStocksDetailByMonthProvid(String[] str) throws GSException{
        String sql=this.getStocksDetailBalanceResultSql4(str);
        try {
            ArrayList array = this.executeQuery(new GSStocksOtherBean(), sql, null);
            return array;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }
    /**
     * 根据纳入结算月份及供应商编号查询所有已纳入结算，结算模式为压库的领用明细(分页)
     * */
    public ArrayList getStocksDetailByMonthProvidpage(String[] str,int pageNo,int pageSize) throws GSException{
        String sql=this.getStocksDetailBalanceResultSql4(str);
        try {
            ArrayList array = this.executeQuery(new GSStocksOtherBean(), sql, null,pageNo,pageSize);
            return array;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }
    /**
     * 根据明细编号查询领用详细信息
     */
    public GSStocksDetailBean getStocksDetailByDetailid(String detailid) throws GSException {
        String sql = "select * from GS_STOCKS_BALANCE_DETAIL where gs_detail_id=?";
        Object[] obj = { detailid };
        GSStocksDetailBean ob = null;
        try {
            ArrayList ar = this.executeQuery(new GSStocksDetailBean(), sql, obj);
            if (ar.size() > 0) {
                ob = (GSStocksDetailBean) ar.get(0);
            }
            return ob;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算明细失败！");
        }
    }

    /***************************************************************************
     * 取得压库结算基本信息表进行审核
     * 
     * @param GSStocksVO
     * 
     * @return
     */
    public ArrayList getStocksResult(GSStocksVO svo) throws GSException {
        String sql = this.getStocksResultSql(svo);
        try {
            ArrayList array = this.executeQuery(new GSStocksOtherBean(), sql, null);
            return array;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }

    /***************************************************************************
     * 分页查询结算基本信息进行审核
     */
    public ArrayList getStocksResultByPage(GSStocksVO svo, int pageNo, int pageSize) throws GSException {
        String sql = this.getStocksResultSql(svo);
        try {
            ArrayList array = this.executeQuery(new GSStocksOtherBean(), sql, null, pageNo, pageSize);
            return array;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }
    /***************************************************************************
     * 分页查询结算基本信息进行审核
     */
    public ArrayList getStocksResultByPage3(GSStocksVO svo, int pageNo, int pageSize) throws GSException {
        String sql = this.getStocksResultSql3(svo);
        try {
            ArrayList array = this.executeQuery(new GSStocksOtherBean(), sql, null, pageNo, pageSize);
            return array;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }
    /***************************************************************************
     * 分页查询结算基本信息进行审核（运营公司）
     */
    public ArrayList getStocksResultByPage2(GSStocksVO svo, int pageNo, int pageSize) throws GSException {
        String sql = this.getStocksResultSql2(svo);
        try {
            ArrayList array = this.executeQuery(new GSStocksOtherBean(), sql, null, pageNo, pageSize);
            return array;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }

    /***************************************************************************
     * 按页面传入参数查询统计行数
     * 
     * @param GSStocksVO
     * 
     * @return int
     **************************************************************************/
    public int getStocksResultCount(GSStocksVO svo) throws GSException {
        String sql = "select count(*)  from (" + this.getStocksResultSql(svo) + ")";
        try {
            int row = this.queryRowNum(sql, null);
            return row;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }
    /***************************************************************************
     * 按页面传入参数查询统计行数
     * 
     * @param GSStocksVO
     * 
     * @return int
     **************************************************************************/
    public int getStocksResultCount3(GSStocksVO svo) throws GSException {
        String sql = "select count(*)  from (" + this.getStocksResultSql3(svo) + ")";
        try {
            int row = this.queryRowNum(sql, null);
            return row;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }
    /***************************************************************************
     * 按页面传入参数查询统计行数(运营公司)
     * 
     * @param GSStocksVO
     * 
     * @return int
     **************************************************************************/
    public int getStocksResultCount2(GSStocksVO svo) throws GSException {
        String sql = "select count(*)  from (" + this.getStocksResultSql2(svo) + ")";
        try {
            int row = this.queryRowNum(sql, null);
            return row;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }

    public int getStocksCount(String sid) throws GSException {
        String sql = "select count(*)  from GS_STOCKS_BALANCE where gs_stocks_id=?";
        Object[] obj = { sid };
        try {
            int row = this.queryRowNum(sql, obj);
            return row;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }

    /***************************************************************************
     * 根据领用单编号查询基本信息
     * 
     * @param stocksid
     * 
     * @return GSStocksBean
     */
    public GSStocksBean getStocksResultBySid(String sid) throws GSException {
        String sql = "select a.*,b.gs_org_name from GS_STOCKS_BALANCE a,GS_ORGANIZATION b where a.gs_workcorp_id=b.gs_org_id and a.gs_stocks_id=?";
        Object[] obj = { sid };
        GSStocksBean sb = null;
        try {
            ArrayList ar = this.executeQuery(new GSStocksBean(), sql, obj);
            if (ar.size() > 0) {
                sb = (GSStocksBean) ar.get(0);
            }
            return sb;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算基本信息失败！");
        }
    }

    public boolean updateStocksDetailState(Connection conn, String did, String org, String remark) throws GSException {
        String sql = "update GS_STOCKS_BALANCE_DETAIL set Gs_org_state=?,Gs_contents=? where gs_detail_id=?";
        Object[] obj = { org, remark, did };
        boolean bl = false;
        try {
            bl = this.executeUpdate(conn, sql, obj);
            return bl;
        } catch (SQLException e) {
            
            GSLogWriter.error(e.getMessage());
            throw new GSException("更新库存领用明细表失败");
        }
    }
    public boolean updateStocksDetailState6(Connection conn,String state, String did, String org, String remark) throws GSException {
        String sql = "update GS_STOCKS_BALANCE_DETAIL set gs_detail_state=?,Gs_org_state=?,Gs_contents=? where gs_detail_id=?";
        Object[] obj = { state,org, remark, did };
        boolean bl = false;
        try {
            bl = this.executeUpdate(conn, sql, obj);
            return bl;
        } catch (SQLException e) {
            
            GSLogWriter.error(e.getMessage());
            throw new GSException("更新库存领用明细表失败");
        }
    }
    public boolean updateStocksDetailState7(Connection conn,String state, String did, String prov, String remark) throws GSException {
        String sql = "update GS_STOCKS_BALANCE_DETAIL set gs_detail_state=?,Gs_prov_state=?,Gs_contents=? where gs_detail_id=?";
        Object[] obj = { state,prov, remark, did };
        boolean bl = false;
        try {
            bl = this.executeUpdate(conn, sql, obj);
            return bl;
        } catch (SQLException e) {
            
            GSLogWriter.error(e.getMessage());
            throw new GSException("更新库存领用明细表失败");
        }
    }
    public boolean updateStocksDetailState2(Connection conn, String did, String pro, String remark) throws GSException {
        String sql = "update GS_STOCKS_BALANCE_DETAIL set Gs_prov_state=?,Gs_contents=? where gs_detail_id=?";
        Object[] obj = { pro, remark, did };
        boolean bl = false;
        try {
            bl = this.executeUpdate(conn, sql, obj);
            return bl;
        } catch (SQLException e) {
            
            GSLogWriter.error(e.getMessage());
            throw new GSException("更新库存领用明细表失败");
        }
    }

    public boolean updateStocksDetailState3(Connection conn, String did, String state, String year, String remark) throws GSException {
        String sql = "update GS_STOCKS_BALANCE_DETAIL set Gs_detail_state=?,Gs_balance_month=? where gs_detail_id=?";
        Object[] obj = { state, year, did };
        boolean bl = false;
        try {
            bl = this.executeUpdate(conn, sql, obj);
            return bl;
        } catch (SQLException e) {
            
            GSLogWriter.error(e.getMessage());
            throw new GSException("更新库存领用明细表失败");
        }
    }

    public boolean updateStocksDetailState4(Connection conn, String did, String state, String pro, String org, String remark) throws GSException {
        String sql = "update GS_STOCKS_BALANCE_DETAIL set Gs_detail_state=?,Gs_prov_state=?,Gs_org_state=?,gs_contents=? where gs_detail_id=?";
        Object[] obj = { state, pro, org, remark, did };
        boolean bl = false;
        try {
            bl = this.executeUpdate(conn, sql, obj);
            return bl;
        } catch (SQLException e) {
            
            GSLogWriter.error(e.getMessage());
            throw new GSException("更新库存领用明细表失败");
        }
    }

    public boolean updateStocksDetailState5(Connection conn, String did, String use, String remark, String content, String price, String count)
            throws GSException {
        String sql = "update GS_STOCKS_BALANCE_DETAIL set gs_detail_unitprice=?,gs_detail_count=?,"
                + "gs_detail_use=?,gs_detail_remark=?,gs_contents=? where gs_detail_id=?";
        Object[] obj = { price, count, use, remark, content, did };
        boolean bl = false;
        try {
            bl = this.executeUpdate(conn, sql, obj);
            return bl;
        } catch (SQLException e) {
            
            GSLogWriter.error(e.getMessage());
            throw new GSException("更新库存领用明细表失败");
        }
    }

    public boolean updateStocks(Connection conn, String sid, String sum) throws GSException {
        String sql = "update GS_STOCKS_BALANCE set gs_stocks_total=gs_stocks_total+? where gs_stocks_id=?";
        Object[] obj = { sum, sid };
        boolean bl = false;
        try {
            bl = this.executeUpdate(conn, sql, obj);
            return bl;
        } catch (SQLException e) {
            
            GSLogWriter.error(e.getMessage());
            throw new GSException("更新库存领用表失败");
        }
    }

    /***************************************************************************
     * 根据领用单编号查询明细信息
     * 
     * @param stocksid
     * 
     * @return
     */
    public ArrayList getStocksDetailResultBySid(String sid) throws GSException {
        String sql = "select * from GS_STOCKS_BALANCE_DETAIL where gs_stocks_id=?";
        Object[] obj = { sid };
        try {
            ArrayList ar = this.executeQuery(new GSStocksDetailBean(), sql, obj);
            return ar;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算明细失败！");
        }
    }

    public GSStocksDetailBean getStocksDetailResultByDid(String did) throws GSException {
        String sql = "select * from GS_STOCKS_BALANCE_DETAIL where gs_detail_id=?";
        Object[] obj = { did };
        GSStocksDetailBean sdbean = null;
        try {
            ArrayList ar = this.executeQuery(new GSStocksDetailBean(), sql, obj);
            if (ar.size() > 0) {
                sdbean = (GSStocksDetailBean) ar.get(0);
            }
            return sdbean;
        } catch (Exception e) {
            GSLogWriter.error(e.getMessage());
            throw new GSException("查询压库结算明细失败！");
        }
    }

    public void save(GSStocksVO svo, ArrayList ar) throws GSException {
        Connection conn = this.getConnetion();
        try {
            conn.setAutoCommit(false);
            boolean bl = this.saveStocks(conn, svo);
            int[] row = this.saveStocksDetail(conn, ar);
            if (bl && row.length > 0) {
                conn.commit();
            }
        } catch (Exception e) {
            
            GSLogWriter.error(e.getMessage());
            try {
                conn.rollback();
            } catch (SQLException e1) {
            }
            throw new GSException("写入压库结算信息错误");
        } finally {
            try {
                conn.setAutoCommit(true);
            } catch (SQLException e) {
            }
            close(conn);
        }
    }

    public boolean saveStocks(Connection conn, GSStocksVO svo) throws GSException {
        String sql = "insert into GS_STOCKS_BALANCE values(?,?,?,?,?,?,?,?,?,?,?)";
        Object[] obj = new Object[11];
        obj[0] = svo.gs_stocks_id;
        obj[1] = svo.gs_workcorp_id;
        obj[2] = svo.gs_stocks_person;
        obj[3] = svo.gs_stocks_dept;
        obj[4] = svo.gs_stocks_total;
        obj[5] = svo.gs_stocks_state;
        obj[6] = svo.gs_stocks_datestart;
        obj[7] = svo.gs_stocks_remark;
        obj[8] = svo.gs_operator_id;
        obj[9] = svo.gs_operator_date;
        obj[10] = svo.gs_shipment_id;
        try {
            boolean bl = this.executeUpdate(conn, sql, obj);
            return bl;
        } catch (SQLException e) {
            
            GSLogWriter.error(e.getMessage());
            throw new GSException("写入压库结算基本信息错误");
        }
    }

    public int[] saveStocksDetail(Connection conn, ArrayList ar) throws GSException {
        String sql = "insert into GS_STOCKS_BALANCE_DETAIL values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < ar.size(); i++) {
                GSStocksDetailVO sdvo = (GSStocksDetailVO) ar.get(i);
                ps.setString(1, sdvo.gs_detail_id);
                ps.setString(2, sdvo.gs_stocks_id);
                ps.setString(3, sdvo.gs_manufacture_id);
                ps.setString(4, sdvo.gs_manufacture_name);
                ps.setString(5, sdvo.gs_manufacture_type);
                ps.setDouble(6, sdvo.gs_detail_unitprice);
                ps.setDouble(7, sdvo.gs_detail_count);
                ps.setString(8, sdvo.gs_detail_use);
                ps.setString(9, sdvo.gs_detail_state);
                ps.setString(10, sdvo.gs_detail_remark);
                ps.setString(11, sdvo.gs_manufacture_category_id);
                ps.setString(12, sdvo.gs_provider_id);
                ps.setString(13, sdvo.gs_concentricity_id);
                ps.setString(14, sdvo.gs_org_state);
                ps.setString(15, sdvo.gs_prov_state);
                ps.setString(16, sdvo.gs_contents);
                ps.setString(17, sdvo.Gs_balance_month);
                ps.setString(18, sdvo.gs_balance_mode);
                ps.addBatch();
            }
            int[] row = ps.executeBatch();
            return row;
        } catch (SQLException e) {
            
            GSLogWriter.error(e.getMessage());
            throw new GSException("写入压库结算明细错误");
        } finally {
            close(ps);
        }
    }

    /***************************************************************************
     * 
     * 压库审核结算（根据领用记录） 明细结算模式是压库 按领用单位生成应收，按供应商生成应付 明细结算模式是买断 按领用单位生成应收 出货单位为中间主体
     * 
     **************************************************************************/
    public boolean doBalanceRecord(String indexs[], String tEndDate1, GSOperatorBean tOperBean, String date[]) throws GSException {
        boolean tFlag = false;

        GSManufactureDAO tMDAO = new GSManufactureDAO();
        String CCZXID = GSSystemConfigurator.getParam("gs.provider.cczxid");
        Connection tConn = null;

        // 是否已结算

        boolean purIsBalance = false; // 采购单位
        boolean proIsBalance = false; // 采购单位
        // 是否存在压库记录
        boolean detailisstocks = false;
        try {
            tConn = this.getConnetion();
            tConn.setAutoCommit(false);

            GSBalanceMsgDAO tBalMsgDAO = new GSBalanceMsgDAO();
            HashMap tInsDeptMap = new HashMap();// 新增订货单位
            HashMap tUpdDeptMap = new HashMap();// 修改订货单位
            HashMap tInsProvMap = new HashMap();// 新增供应商
            HashMap tUpdProvMap = new HashMap();// 修改供应商
            // 3:分别对每个被选订单明细作订货单位结算和供应商结算
            for (int i = 0; i < indexs.length; i++) {
                GSStocksDetailBean debean = this.getStocksDetailByDetailid(indexs[i]);
                // 根据领用编号取得运营公司编号（领用公司）
                GSStocksBean sbean = this.getStocksResultBySid(debean.getGs_stocks_id());
                // 3.1:如果该订单明细已经做过订货单位结算，则不再进行订货单位结算
                if (GSBaseConstants.UNBALANCE_STATUS.equals(debean.getGs_org_state())) {
                    // 如果该订单明细所属的订货单位已经进行了本月结算则不再做本月结算
                    ArrayList tTempList1 = tBalMsgDAO.getBalanceMsg(sbean.getGs_workcorp_id(), tEndDate1);
                    if (tTempList1.size() > 0 && ((GSBalanceMsgBean) tTempList1.get(0)).getGs_balance_sign().equals(GSBaseConstants.BALANCE_STATUS)) {
                        purIsBalance = true;
                        GSOrganizationDAO od = new GSOrganizationDAO();
                        GSOrganizationBean ob = od.getOrg(sbean.getGs_workcorp_id());
                        if (ob == null)
                            ob = new GSOrganizationBean();
                        GSLogWriter.error(this, "订货单位" + sbean.getGs_workcorp_id() + (ob.getGs_org_name()) + "已经完成" + tEndDate1 + "结算，无法将明细纳入当月结算");
                        throw new GSException("订货单位[" + ob.getGs_org_name() + "]已经完成" + tEndDate1 + "结算，无法将明细纳入当月结算。");
                    } else {// 对该订单明细执行纳入当月结算
                        // 累加产品金额
                        BigDecimal temp = new BigDecimal(debean.getGs_detail_unitprice()).multiply(new BigDecimal(debean.getGs_detail_count()));
                        if (tTempList1.size() == 0) {// 需要新增一条订货单位纳入结算记录
                            if (tInsDeptMap.containsKey(sbean.getGs_workcorp_id())) {
                                BigDecimal tempBD = (BigDecimal) tInsDeptMap.get(sbean.getGs_workcorp_id());
                                tempBD = tempBD.add(temp);
                                tInsDeptMap.put(sbean.getGs_workcorp_id(), tempBD);
                            } else {
                                tInsDeptMap.put(sbean.getGs_workcorp_id(), temp);
                            }
                        } else {// 需要修改现有的订货单位纳入结算记录
                            if (tUpdDeptMap.containsKey(sbean.getGs_workcorp_id())) {
                                BigDecimal tempBD = (BigDecimal) tUpdDeptMap.get(sbean.getGs_workcorp_id());
                                tempBD = tempBD.add(temp);
                                tUpdDeptMap.put(sbean.getGs_workcorp_id(), tempBD);
                            } else {
                                tUpdDeptMap.put(sbean.getGs_workcorp_id(), temp);
                            }

                        }
                        // 修改该笔订单明细为已纳入结算
                        String did = debean.getGs_detail_id();
                        String state = GSBaseConstants.BALANCE_STATUS;
                        String remark = "[ " + DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss") + " 明细已纳入订货单位结算]";
                        // 修改订单明细的订货单位结算状态
                        tFlag = this.updateStocksDetailState(tConn, did, state, remark);
                        if (!tFlag) {
                            GSLogWriter.error(this, "修改明细为明细已纳入结算状态失败");
                            throw new GSException("修改明细为明细已纳入结算状态失败");
                        }
                    }
                }
                // 判断订单结算模式是否是压库（1）,压库产生应付,买断只产生应收
                if (debean.getGs_balance_mode().equals(GSBaseConstants.BALANCE_MODE2)) {
                    detailisstocks = true;
                    // 3.2:如果该订单明细已经做过供应商结算，则不再进行供应商结算
                    if (GSBaseConstants.UNBALANCE_STATUS.equals(debean.getGs_prov_state())) {
                        // 如果该订单明细所属的供应商已经进行了本月结算则不再做本月结算
                        GSManufactureBean tMBean = tMDAO.getManufacture(debean.getGs_manufacture_id());
                        String curProviderId = "0".equals(tMBean.getGs_is_tradeflat_manufacture()) ? CCZXID : debean.getGs_provider_id();
                        ArrayList tTempList2 = tBalMsgDAO.getBalanceMsg(curProviderId, tEndDate1);
                        if (tTempList2.size() > 0 && ((GSBalanceMsgBean) tTempList2.get(0)).getGs_balance_sign().equals(GSBaseConstants.BALANCE_STATUS)) {
                            proIsBalance = true;
                            GSProviderDAO pd = new GSProviderDAO();
                            GSProviderBean pb = pd.queryProvider(curProviderId);
                            if (pb == null)
                                pb = new GSProviderBean();
                            GSLogWriter.error(this, "供应商" + curProviderId + (pb.getGs_provider_shortname()) + "已经完成" + tEndDate1 + "结算，无法将明细纳入当月结算");
                            throw new GSException("供应商[" + pb.getGs_provider_shortname() + "]已经完成" + tEndDate1 + "结算，无法将明细纳入当月结算。");
                        } else {// 对该订单明细执行纳入当月结算
                            // 累加产品金额
                            BigDecimal temp = new BigDecimal(debean.getGs_detail_unitprice()).multiply(new BigDecimal(debean.getGs_detail_count()));
                            if (tTempList2.size() == 0) {// 需要新增一条供应商纳入结算记录
                                if (tInsProvMap.containsKey(curProviderId)) {
                                    BigDecimal tempBD = (BigDecimal) tInsProvMap.get(curProviderId);
                                    tempBD = tempBD.add(temp);
                                    tInsProvMap.put(curProviderId, tempBD);
                                } else {
                                    tInsProvMap.put(curProviderId, temp);
                                }
                            } else {// 需要修改现有的供应商纳入结算记录
                                if (tUpdProvMap.containsKey(curProviderId)) {
                                    BigDecimal tempBD = (BigDecimal) tUpdProvMap.get(curProviderId);
                                    tempBD = tempBD.add(temp);
                                    tUpdProvMap.put(curProviderId, tempBD);
                                } else {
                                    tUpdProvMap.put(curProviderId, temp);
                                }
                            }
                        }
                    }
                }
                // ===============修改该笔订单明细为已纳入结算
                String did = debean.getGs_detail_id();
                String state = GSBaseConstants.BALANCE_STATUS;
                String remark = "[ " + DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss") + " 明细已纳入供应商结算]";
                // 修改订单明细的供应商结算状态
                tFlag = this.updateStocksDetailState2(tConn, did, state, remark);
                if (!tFlag) {
                    GSLogWriter.error(this, "修改明细已纳入供应商结算状态失败");
                    throw new GSException("修改明细已纳入供应商结算状态失败");
                }
                // ===============================
                // 修改明细总状态为已结算
                did = debean.getGs_detail_id();
                state = GSBaseConstants.BALANCE_STATUS;
                String yearmonth = tEndDate1;
                remark = "[ " + DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss") + " 明细已纳入结算]";
                // 修改订单明细的结算状态
                tFlag = this.updateStocksDetailState3(tConn, did, state, yearmonth, remark);
                if (!tFlag) {
                    GSLogWriter.error(this, "修改明细已结算状态失败");
                    throw new GSException("修改明细已结算状态失败");
                }
            }

            GSBalanceInfoVO tVO = new GSBalanceInfoVO();

            // 4:为订货单位进行结算录入
            // 4.1:需要新增订货单位结算信息的
            Iterator it3 = tInsDeptMap.keySet().iterator();
            while (it3.hasNext()) {
                String deptId = it3.next().toString();
                BigDecimal totalAmt = (BigDecimal) tInsDeptMap.get(deptId);
                tVO.deptId = deptId;
                tVO.balanceYM = tEndDate1;
                tVO.startDate = DateUtil.formatDate(date[0]);// aForm.getBalanceStrDate()
                tVO.endDate = DateUtil.formatDate(date[1]);// aForm.getBalanceEndDate()
                tVO.totalAmt = totalAmt.doubleValue();
                tVO.actualBalanceDept = tVO.deptId;
                tVO.payType = GSBaseConstants.RECEIVE_TYPE;
                tVO.balanceFlag = GSBaseConstants.UNBALANCE_STATUS;
                tVO.operatorId = tOperBean.getGs_oper_id();
                tVO.operateDate = DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss");
                tVO.deptType = GSBaseConstants.BALANCE_ORDERDEPT_TYPE;
                tVO.balanceMSG = GSBaseConstants.BALANCE_MSG;
                tFlag = tBalMsgDAO.insertBalanceInfo2(tVO, tConn);
                if (!tFlag) {
                    GSLogWriter.error(this, "写结算信息表失败,订货单位号为：" + deptId);
                    throw new GSException(GSException.E40051, GSException.E40051_MSG);
                }
            }
            // 4.2:需要修改订货单位结算记录的
            Iterator it4 = tUpdDeptMap.keySet().iterator();
            while (it4.hasNext()) {
                String deptId = it4.next().toString();
                BigDecimal deptAmt = (BigDecimal) tUpdDeptMap.get(deptId);

                String[] tBalMsg = new String[] { deptId, deptAmt.toString() };
                tFlag = tBalMsgDAO.updateBalanceSum(tConn, tBalMsg, tEndDate1, tOperBean.getGs_oper_id(), GSBaseConstants.BALANCE_MSG);
                if (!tFlag) {
                    GSLogWriter.error(this, "修改结算信息表中已存在供应商结算金额失败");
                    throw new GSException(GSException.E40006, GSException.E40006_MSG);
                }
            }
            if (detailisstocks) {
                // 5:为供应商进行结算录入
                // 5.1:需要新增供应商结算记录的
                Iterator it1 = tInsProvMap.keySet().iterator();
                while (it1.hasNext()) {
                    String providerId = it1.next().toString();
                    BigDecimal provAmt = (BigDecimal) tInsProvMap.get(providerId);

                    tVO = new GSBalanceInfoVO();
                    tVO.deptId = providerId;
                    tVO.balanceYM = tEndDate1;
                    tVO.startDate = DateUtil.formatDate(date[0]);// aForm.getBalanceStrDate()
                    tVO.endDate = DateUtil.formatDate(date[1]);// aForm.getBalanceEndDate()
                    tVO.totalAmt = provAmt.doubleValue();
                    tVO.payType = GSBaseConstants.PAYMENT_TYPE;
                    tVO.balanceFlag = GSBaseConstants.UNBALANCE_STATUS;
                    tVO.operatorId = tOperBean.getGs_oper_id();
                    tVO.operateDate = DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss");
                    tVO.deptType = GSBaseConstants.BALANCE_PROVIDER_TYPE;
                    tVO.balanceMSG = GSBaseConstants.BALANCE_MSG;
                    tFlag = tBalMsgDAO.insertBalanceInfo2(tVO, tConn);
                    if (!tFlag) {
                        GSLogWriter.error(this, "写结算信息表失败");
                        throw new GSException(GSException.E40006, GSException.E40006_MSG);
                    }
                }
                // 5.2:需要修改供应商结算记录的
                Iterator it2 = tUpdProvMap.keySet().iterator();
                while (it2.hasNext()) {
                    String providerId = it2.next().toString();
                    BigDecimal provAmt = (BigDecimal) tUpdProvMap.get(providerId);

                    String[] tBalMsg = new String[] { providerId, provAmt.toString() };
                    tFlag = tBalMsgDAO.updateBalanceSum(tConn, tBalMsg, tEndDate1, tOperBean.getGs_oper_id(), GSBaseConstants.BALANCE_MSG);
                    if (!tFlag) {
                        GSLogWriter.error(this, "修改结算信息表中已存在供应商结算金额失败");
                        throw new GSException(GSException.E40006, GSException.E40006_MSG);
                    }
                }
            }
            // 采购单位没纳入结算
            if (purIsBalance == false&&proIsBalance == false) {
                // 6:检查是否有效的结算过程
                if (tInsDeptMap.isEmpty()&&tUpdDeptMap.isEmpty()) {
                    GSLogWriter.error(this, "无可写入到结算信息表的数据，需检查数据或程序");
                    throw new GSException("无可写入到结算信息表的数据，需检查数据或程序");
                }else if(detailisstocks&&tInsProvMap.isEmpty()&&tUpdProvMap.isEmpty()){ 
                    GSLogWriter.error(this, "无可写入到结算信息表的数据，需检查数据或程序");
                    throw new GSException("无可写入到结算信息表的数据，需检查数据或程序");
                }else {
                    if (tFlag) {
                        tConn.commit();
                        GSLogWriter.info(this, "成功新增或修改结算信息表中供应商的结算金额");
                    }
                }
            }

        } catch (GSException ge) {
            if (tConn != null) {
                try {
                    tConn.rollback();
                } catch (SQLException se) {
                    se.printStackTrace();
                }
            }
            throw ge;
        } catch (Exception e) {
            if (tConn != null) {
                try {
                    tConn.rollback();
                } catch (SQLException se) {
                    se.printStackTrace();
                }
            }
            GSLogWriter.error(this, e.toString());
            throw new GSException(e.toString());
        } finally {
            try {
                if (tConn != null) {
                    tConn.setAutoCommit(true);
                    tConn.close();
                }
            } catch (SQLException se) {
                se.printStackTrace();
            }
        }
        return tFlag;

    }

    // 暂不结算
    public boolean stocksBanlanceBack(String aDeptId, String aBalanceYM, GSOperatorBean aOper, String[] indexs) throws GSException {
        boolean bl = false;
        Connection conn = this.getConnetion();
        GSBalanceMsgDAO tBalMsgDAO = new GSBalanceMsgDAO();
        String[] tBalMsg = new String[3];
        double bm = 0;
        try {
            conn.setAutoCommit(false);
            // 查找领用明细表相关记录
            for (int i = 0; i < indexs.length; i++) {
                GSStocksDetailBean sdbean = this.getStocksDetailByDetailid(indexs[i]);
                // 修改运营公司金额
                bm = sdbean.getGs_detail_unitprice() * sdbean.getGs_detail_count();
                tBalMsg[0] = aDeptId;
                tBalMsg[1] = String.valueOf(-bm);
                bl = tBalMsgDAO.updateBalanceSum(conn, tBalMsg, aBalanceYM, aOper.getGs_oper_id(), GSBaseConstants.BALANCE_MSG);
              //如果结算模式为压库
                if (sdbean.getGs_balance_mode().equals(GSBaseConstants.BALANCE_MODE2)) {
                    // 修改供应商进金额
                    tBalMsg[0] = sdbean.getGs_provider_id();
                    bl = tBalMsgDAO.updateBalanceSum(conn, tBalMsg, aBalanceYM, aOper.getGs_oper_id(), GSBaseConstants.BALANCE_MSG);
                }
                // 删除结算金额为0的记录
                tBalMsgDAO.deleteZero(conn);
                // 更新压库明细表
                String remark = "[ " + DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss") + " 明细暂不结算]";
                bl = this.updateStocksDetailState4(conn, sdbean.getGs_detail_id(), "0", "0", "0", remark);
            }
            conn.commit();
            return bl;
        } catch (Exception e) {
            
            GSLogWriter.error(e.getMessage());
            try {
                conn.rollback();
            } catch (SQLException e1) {
                
                e1.printStackTrace();
            }
            throw new GSException("暂不结算操作失败");
        } finally {
            close(conn);
        }
    }

    // 作废
    public boolean stocksBanlanceDel(String aDeptId, String aBalanceYM, GSOperatorBean aOper, String[] indexs,String str) throws GSException {
        boolean bl = false;
        Connection conn = this.getConnetion();
        GSBalanceMsgDAO tBalMsgDAO = new GSBalanceMsgDAO();
        String[] tBalMsg = new String[3];
        double bm = 0;
        try {
            conn.setAutoCommit(false);
            // 查找领用明细表相关记录
            for (int i = 0; i < indexs.length; i++) {
                GSStocksDetailBean sdbean = this.getStocksDetailByDetailid(indexs[i]);
                // 修改运营公司金额
                bm = sdbean.getGs_detail_unitprice() * sdbean.getGs_detail_count();
                tBalMsg[0] = aDeptId;
                tBalMsg[1] = String.valueOf(-bm);
                bl = tBalMsgDAO.updateBalanceSum(conn, tBalMsg, aBalanceYM, aOper.getGs_oper_id(), GSBaseConstants.BALANCE_MSG);
                //如果结算模式为压库
                if (sdbean.getGs_balance_mode().equals(GSBaseConstants.BALANCE_MODE2)) {
                    // 修改供应商进金额
                    tBalMsg[0] = sdbean.getGs_provider_id();
                    bl = tBalMsgDAO.updateBalanceSum(conn, tBalMsg, aBalanceYM, aOper.getGs_oper_id(), GSBaseConstants.BALANCE_MSG);
                }
                // 删除结算金额为0的记录
                tBalMsgDAO.deleteZero(conn);
                // 更新压库明细表
                String remark = " 作废:[ " + DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss")+"]";
                if (str.trim().length()<=30) {
                    remark=str+remark;
                }else{
                    remark=str;
                }
                bl = this.updateStocksDetailState4(conn, sdbean.getGs_detail_id(), "2", "2", "2", remark);
            }
            conn.commit();
            return bl;
        } catch (Exception e) {
            
            GSLogWriter.error(e.getMessage());
            try {
                conn.rollback();
            } catch (SQLException e1) {
                
                e1.printStackTrace();
            }
            throw new GSException("作废操作失败");
        } finally {
            close(conn);
        }
    }

    // 调整
    public boolean stocksBanlanceUpdate(String aDeptId, String aBalanceYM, GSOperatorBean aOper, String[] indexs, String[] str) throws GSException {
        boolean bl = false;
        Connection conn = this.getConnetion();
        GSBalanceMsgDAO tBalMsgDAO = new GSBalanceMsgDAO();
        String[] tBalMsg = new String[3];
        double temp0 = Double.parseDouble(str[0]);
        double temp1 = Double.parseDouble(str[1]);
        double temp2 = Double.parseDouble(str[2]);
        double temp3 = Double.parseDouble(str[3]);
        double bm = 0;
        try {
            conn.setAutoCommit(false);
            // 查找领用明细表相关记录
            for (int i = 0; i < indexs.length; i++) {
                GSStocksDetailBean sdbean = this.getStocksDetailByDetailid(indexs[i]);
                // 修改运营公司金额
                bm = (temp0 * temp1) - (temp2 * temp3);
                tBalMsg[0] = aDeptId;
                tBalMsg[1] = String.valueOf(bm);
                bl = tBalMsgDAO.updateBalanceSum(conn, tBalMsg, aBalanceYM, aOper.getGs_oper_id(), GSBaseConstants.BALANCE_MSG);
                //如果结算模式为压库
                if (sdbean.getGs_balance_mode().equals(GSBaseConstants.BALANCE_MODE2)) {
                    // 修改供应商进金额
                    tBalMsg[0] = sdbean.getGs_provider_id();
                    bl = tBalMsgDAO.updateBalanceSum(conn, tBalMsg, aBalanceYM, aOper.getGs_oper_id(), GSBaseConstants.BALANCE_MSG);
                }
                // 删除结算金额为0的记录
                tBalMsgDAO.deleteZero(conn);
                // 更新压库明细表
                String cont = "[ " + DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss") + " 明细已调整]";
                bl = this.updateStocksDetailState5(conn, sdbean.getGs_detail_id(), str[4], str[5], cont, str[0], str[1]);
                // 更新压库总表总价
                bl = this.updateStocks(conn, sdbean.getGs_stocks_id(), tBalMsg[1]);
            }
            conn.commit();
            return bl;
        } catch (Exception e) {
            
            GSLogWriter.error(e.getMessage());
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                
                e1.printStackTrace();
            }
            throw new GSException("调整操作失败");
        } finally {
            close(conn);
        }
    }

    // 查询调整
    public boolean stocksBanlanceUpdate(GSOperatorBean aOper, String[] indexs, String[] str) throws GSException {
        boolean bl = false;
        Connection conn = this.getConnetion();
        String[] tBalMsg = new String[3];
        double temp0 = Double.parseDouble(str[0]);
        double temp1 = Double.parseDouble(str[1]);
        double temp2 = Double.parseDouble(str[2]);
        double temp3 = Double.parseDouble(str[3]);
        double bm = 0;
        try {
            conn.setAutoCommit(false);
            // 查找领用明细表相关记录
            for (int i = 0; i < indexs.length; i++) {
                GSStocksDetailBean sdbean = this.getStocksDetailByDetailid(indexs[i]);
                bm = (temp0 * temp1) - (temp2 * temp3);
                tBalMsg[1] = String.valueOf(bm);
                // 更新压库明细表
                String cont = "[ " + DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss") + " 调整]";
                bl = this.updateStocksDetailState5(conn, sdbean.getGs_detail_id(), str[4], str[5], cont, str[0], str[1]);
                // 更新压库总表总价
                bl = this.updateStocks(conn, sdbean.getGs_stocks_id(), tBalMsg[1]);
            }
            conn.commit();
            return bl;
        } catch (Exception e) {
            
            GSLogWriter.error(e.getMessage());
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                
                e1.printStackTrace();
            }
            throw new GSException("查询调整操作失败");
        } finally {
            close(conn);
        }
    }

    // 查询作废
    public boolean stocksBanlanceDel(GSOperatorBean aOper, String[] indexs) throws GSException {
        boolean bl = false;
        Connection conn = this.getConnetion();
        try {
            conn.setAutoCommit(false);
            // 查找领用明细表相关记录
            for (int i = 0; i < indexs.length; i++) {
                GSStocksDetailBean sdbean = this.getStocksDetailByDetailid(indexs[i]);
                String remark = "[ " + DateUtil.getCurrentDate("yyyy-MM-dd hh:mm:ss") + " 已作废]";
                bl = this.updateStocksDetailState4(conn, sdbean.getGs_detail_id(), "2", "2", "2", remark);
            }
            conn.commit();
            return bl;
        } catch (Exception e) {
            
            GSLogWriter.error(e.getMessage());
            try {
                conn.rollback();
            } catch (SQLException e1) {
                
                e1.printStackTrace();
            }
            throw new GSException("查询作废操作失败");
        } finally {
            close(conn);
        }
    }
    public boolean updateorgprov() throws GSException{
        String sql="update gs_stocks_balance_detail set gs_org_state='3',gs_prov_state='3' where gs_org_state='4' and gs_prov_state='4'";
        boolean bl=false;
        try {
           bl =this.executeUpdate(sql, null);
        } catch (SQLException e) {
            
            throw new GSException("更新领用明细表错误");
        }
        return bl;
    }
}
